﻿using System;
using System.Data;
using CoreLab.Oracle; //using Devart.Data.Oracle; //using Oracle.DataAccess.Client;
using System.Web;
using System.Web.Caching;


using LMD.Constant;
using LMD.Lib.Oracle;

namespace Cached
{
    /// <summary>
    /// Summary description for CustomerTable
    /// </summary>
    public class CustomerTable : CacheAbstraction
    {
        public DataTable GetCustomer(int iEventId, bool bSelected, int iFilterEventId)
        {
            return GetCustomer(iEventId, bSelected, iFilterEventId, false);
        }
        public DataTable GetCustomer(int iEventId, bool bSelected, int iFilterEventId, bool bForceRefresh)
        {
            String sxSelected = bSelected ? "NOT" : "";
            String sxCacheKey = KCache.V_ICP_CUSTOMER_EVENT + 
                sxSelected + "_" + iEventId.ToString() + "_" + iFilterEventId.ToString();

            if(bForceRefresh)
            {
                HttpRuntime.Cache.Remove(sxCacheKey);
            }

            if (HttpRuntime.Cache[sxCacheKey] == null)
            {
                DBLib dbl = new DBLib();
                DataTable dt;

                OracleParameter prmID = new OracleParameter();
                prmID.ParameterName = "prmID";
                prmID.OracleDbType = OracleDbType.Number;
                prmID.Value = iEventId;

                String sxQuery = String.Empty;
                if (iFilterEventId < 0)
                {
                    sxQuery = String.Format(
                        @"
                        select
                            v.*,
                            (
                                SELECT   MAX (e.event_date)
                                FROM      t_relation_invitation i
                                LEFT JOIN
                                t_relation_event e
                                ON i.t_relation_event_id = e.t_relation_event_id
                                WHERE   
                                	i.customer_id = v.customer_id
                                	and i.status_hadir = 'Y'
                            ) last_event_date,
                            decode(nvl(t1.T_RELATION_INVITATION_ID,0), 0,'T', 'Y') IS_INVITED,
                            decode(t1.status_hadir, null,'T', 'Y') IS_ATTENDED,
                            decode(t1.status_confirm,null,'T', 'Y') IS_CONFIRMED,
                            decode(t1.status_reject, 'Y' ,'Y', 'T') IS_BLACKLISTED,
                            t1.code,
                            t1.event_date,
							'' X_COUNT,
                            '' X_MAX
                        from
                            V_ICP_CUSTOMER_EVENT v
                        left join
                        (
                            select 
                            inv.t_relation_invitation_id,
                            inv.customer_id,
                            inv.status_hadir,
                            inv.status_confirm,
                            inv.status_reject,
                            evt.code,
                            evt.event_date,
                            evt.t_relation_event_id
                            from
                            t_relation_invitation inv
                            left join
                            t_relation_event evt
                            on
                            inv.t_relation_event_id = evt.t_relation_event_id
                            order by
                            inv.customer_id asc
                        ) t1
                        on
                            v.CUSTOMER_ID = t1.CUSTOMER_ID
                            and t1.T_RELATION_EVENT_ID = :prmID
                        where
                            t1.CUSTOMER_ID is {0} null", sxSelected);

//                    sxQuery = String.Format(
//                        @"
//                        select
//                            v.*,
//                            t.T_RELATION_EVENT_ID,
//                            t2.CODE,
//                            decode(nvl(t.T_RELATION_INVITATION_ID,0), 0,'T', 'Y') IS_INVITED,
//                            decode(t.STATUS_HADIR,'Y','Y','T') IS_ATTENDED
//                        from 
//                            V_ICP_CUSTOMER_EVENT v
//                        left join 
//                            T_RELATION_INVITATION t
//                        on 
//                            v.CUSTOMER_ID = t.CUSTOMER_ID
//                            and t.T_RELATION_EVENT_ID = :prmID
//                        left join
//                            t_relation_event t2
//                        on 
//                            t.T_RELATION_EVENT_ID = t2.T_RELATION_EVENT_ID                        
//                        where                                         
//                            t.CUSTOMER_ID IS {0} NULL ", sxSelected);
                
                    dt = dbl.GetDataTable(sxQuery, prmID);
                }
                else
                {
                    OracleParameter prmFilterID = new OracleParameter();
                    prmFilterID.ParameterName = "prmFilterID";
                    prmFilterID.OracleDbType = OracleDbType.Number;
                    prmFilterID.Value = iFilterEventId;

                    sxQuery = String.Format(
                        @"
                        select * from
                        (
                        select
                            tbl1.*,
							(
                                SELECT   MAX (e.event_date)
                                FROM      t_relation_invitation i
                                LEFT JOIN
                                t_relation_event e
                                ON i.t_relation_event_id = e.t_relation_event_id
                                WHERE   
                                    i.customer_id = tbl1.customer_id
                                    and i.status_hadir = 'Y'
                            ) last_event_date,
                            max(decode(tbl1.x_COUNT, 1, 1,0)) over (partition by tbl1.CUSTOMER_ID) x_MAX
                        from
                            (
                                select
                                    v.*,
                                    /*(
                                        SELECT   MAX (e.event_date)
                                        FROM      t_relation_invitation i
                                        LEFT JOIN
                                        t_relation_event e
                                        ON i.t_relation_event_id = e.t_relation_event_id
                                        WHERE   
                                            i.customer_id = v.customer_id
                                            and i.status_hadir = 'Y'
                                    ) last_event_date,*/
                                    decode(nvl(t1.T_RELATION_INVITATION_ID,0), 0,'T', 'Y') IS_INVITED,
                                    decode(t1.status_hadir, null,'T', 'Y') IS_ATTENDED,
                                    decode(t1.status_confirm,null,'T', 'Y') IS_CONFIRMED,
                                    decode(t1.status_reject, 'Y' ,'Y', 'T') IS_BLACKLISTED,
                                    t1.code,
                                    t1.event_date,
                                    decode(t1.code,null, 0, 1) x_COUNT
                                from
                                    V_ICP_CUSTOMER_EVENT v
                                left join
                                (
                                    select 
                                    inv.t_relation_invitation_id,
                                    inv.customer_id,
                                    inv.status_hadir,
                                    inv.status_confirm,
                                    inv.status_reject,
                                    evt.code,
                                    evt.event_date,
                                    evt.t_relation_event_id
                                    from
                                    t_relation_invitation inv
                                    left join
                                    t_relation_event evt
                                    on
                                    inv.t_relation_event_id = evt.t_relation_event_id
                                    order by
                                    inv.customer_id asc
                                ) t1
                                on
                                    v.CUSTOMER_ID = t1.CUSTOMER_ID
                                    and t1.T_RELATION_EVENT_ID = :prmID
                                where
                                    t1.CUSTOMER_ID is null
                            UNION
                                select
                                    v.*,
                                    /*(
                                        SELECT   MAX (e.event_date)
                                        FROM      t_relation_invitation i
                                        LEFT JOIN
                                        t_relation_event e
                                        ON i.t_relation_event_id = e.t_relation_event_id
                                        WHERE   
                                            i.customer_id = v.customer_id
                                            and i.status_hadir = 'Y'
                                    ) last_event_date,*/
                                    decode(nvl(t1.T_RELATION_INVITATION_ID,0), 0,'T', 'Y') IS_INVITED,
                                    decode(t1.status_hadir, null,'T', 'Y') IS_ATTENDED,
                                    decode(t1.status_confirm,null,'T', 'Y') IS_CONFIRMED,
                                    decode(t1.status_reject, 'Y' ,'Y', 'T') IS_BLACKLISTED,
                                    t1.code,
                                    t1.event_date,
                                    decode(t1.code,null, 0, 1) x_COUNT
                                from
                                    V_ICP_CUSTOMER_EVENT v
                                left join
                                (
                                    select 
                                    inv.t_relation_invitation_id,
                                    inv.customer_id,
                                    inv.status_hadir,
                                    inv.status_confirm,
                                    inv.status_reject,
                                    evt.code,
                                    evt.event_date,
                                    evt.t_relation_event_id
                                    from
                                    t_relation_invitation inv
                                    left join
                                    t_relation_event evt
                                    on
                                    inv.t_relation_event_id = evt.t_relation_event_id
                                    order by
                                    inv.customer_id asc
                                ) t1
                                on
                                    v.CUSTOMER_ID = t1.CUSTOMER_ID
                                    and t1.T_RELATION_EVENT_ID = :prmFilterID
                                where
                                    t1.CUSTOMER_ID is not null
                            )tbl1
                        )
                        where 
                            x_COUNT = x_MAX
                        ", sxSelected);
//                    sxQuery = String.Format(
//                        @"
//                        select
//                            v.*,
//                            (
//                                SELECT   MAX (e.event_date)
//                                FROM      t_relation_invitation i
//                                LEFT JOIN
//                                t_relation_event e
//                                ON i.t_relation_event_id = e.t_relation_event_id
//                                WHERE   
//                                	i.customer_id = v.customer_id
//                                	and i.status_hadir = 'Y'
//                            ) last_event_date,
//                            decode(nvl(t1.T_RELATION_INVITATION_ID,0), 0,'T', 'Y') IS_INVITED,
//                            decode(nvl(t1.status_hadir,0), 0,'T', 'Y') IS_ATTENDED,
//                            decode(nvl(t1.status_confirm,0), 0,'T', 'Y') IS_CONFIRMED,
//                            decode(t1.status_reject, 'Y' ,'Y', 'T') IS_BLACKLISTED,
//                            t1.code,
//                            t1.event_date
//                        from
//                            V_ICP_CUSTOMER_EVENT v
//                        left join
//                        (
//                            select 
//                            inv.t_relation_invitation_id,
//                            inv.customer_id,
//                            inv.status_hadir,
//                            inv.status_confirm,
//                            inv.status_reject,
//                            evt.code,
//                            evt.event_date,
//                            evt.t_relation_event_id
//                            from
//                            t_relation_invitation inv
//                            left join
//                            t_relation_event evt
//                            on
//                            inv.t_relation_event_id = evt.t_relation_event_id
//                            order by
//                            inv.customer_id asc
//                        ) t1
//                        on
//                            v.CUSTOMER_ID = t1.CUSTOMER_ID
//                            and t1.T_RELATION_EVENT_ID = :prmFilterID
//                        where
//                            t1.CUSTOMER_ID is not null", sxSelected);

//                    sxQuery = String.Format(
//                        @"
//                        select
//                            v.*,
//                            t1.T_RELATION_EVENT_ID,
//                            t2.CODE,
//                            decode(nvl(t.T_RELATION_INVITATION_ID,0), 0,'T', 'Y') IS_INVITED,
//                            decode(t.STATUS_HADIR,'Y','Y','T') IS_ATTENDED
//                        from 
//                            V_ICP_CUSTOMER_EVENT v
//                        left join 
//                            T_RELATION_INVITATION t
//                        on 
//                            v.CUSTOMER_ID = t.CUSTOMER_ID
//                            and t.T_RELATION_EVENT_ID = :prmID
//                        left join
//                            t_relation_invitation t1
//                        on
//                            v.CUSTOMER_ID = t1.CUSTOMER_ID
//                        left join
//                            t_relation_event t2
//                        on 
//                            t1.T_RELATION_EVENT_ID = t2.T_RELATION_EVENT_ID
//                        where 
//                            t1.T_RELATION_EVENT_ID = :prmFilterID 
//                            and                   
//                            t.CUSTOMER_ID IS {0} NULL", sxSelected);

                    dt = dbl.GetDataTable(sxQuery, prmID, prmFilterID);
                    //dt = dbl.GetDataTable(sxQuery, prmID, prmFilterID);
                }
                AddToCache(sxCacheKey, dt, CacheType.OneHour);
                
            }
            return (DataTable)HttpRuntime.Cache[sxCacheKey];
        }        

        public override String GetCacheFilename()
        {
            return KCache.Filename.EVENT_CUSTOMER;
        }
    }
}